Movie Collection Analysis¶
Load packages¶
In [3]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import pyodbc
from IPython.display import Image
%matplotlib inline
Load files¶
In [4]:
pwd
Out[4]:
In [5]:
# load csv files as dataframes
df_writer = pd.read_csv("original_data\\Movie_Writer.csv")
df_actor = pd.read_csv("original_data\\Movie_Actors.csv")
df_rating = pd.read_csv("original_data\\Movie_AdditionalRating.csv")
df_movie = pd.read_csv("original_data\\Movie_Movies.csv")
df_genre = pd.read_csv("original_data\\Movie_Genres.csv")
Data Exploration and Cleaning for importing data into SQL¶
First I do a very simple exploratory analysis to understand how all the data is structued and related and I also clean the data in order to put the data into SQL
Dimensionality and raw data check¶
In [6]:
for i in [df_writer, df_actor, df_rating, df_movie, df_genre]:
print(i.shape)
Movie_Writer.csv Data¶
In [7]:
df_writer.info()
In [8]:
df_writer.head()
Out[8]:
In [9]:
df_writer['Responsibility'].value_counts()
Out[9]:
In [10]:
df_writer[df_writer['imdbID'].duplicated()]
Out[10]:
Movie_Actors¶
In [11]:
df_actor.info()
In [12]:
df_actor.head()
Out[12]:
In [13]:
df_actor[df_actor['imdbID'].duplicated()]
Out[13]:
Movie_AdditionalRating¶
In [14]:
df_rating.info()
In [15]:
df_rating.head()
Out[15]:
In [16]:
df_rating[df_rating['imdbID'].duplicated()]
Out[16]:
In [17]:
df_rating[df_rating['imdbID'].duplicated()]
Out[17]:
Movie_Movies¶
In [18]:
df_movie.info()
In [19]:
df_movie.head(3)
Out[19]:
In [20]:
df_movie.tail(3)
Out[20]:
In [21]:
df_movie['Type'].value_counts()
Out[21]:
In [22]:
df_movie['DVD'].value_counts()
Out[22]:
In [23]:
df_movie[df_movie['imdbID'].duplicated()]
Out[23]:
Movie_Genres¶
In [24]:
df_genre.head()
Out[24]:
In [25]:
df_genre.info()
In [26]:
df_genre['Genre'].value_counts()
Out[26]:
In [27]:
df_genre[df_genre['imdbID'].duplicated()]
Out[27]:
Data Cleaing¶
Remove "Unnamed: 0" Column from df_writer, df_actor, df_rating, df_genre¶
In [28]:
df_writer.drop("Unnamed: 0", axis = 1, inplace = True)
df_actor.drop("Unnamed: 0", axis = 1, inplace = True)
df_rating.drop("Unnamed: 0", axis = 1, inplace = True)
df_genre.drop("Unnamed: 0", axis = 1, inplace = True)
Save the cleaned files as csv file again.¶
In [29]:
df_writer.to_csv("cleaned_data\\movie_writer_2.csv", index=False)
df_actor.to_csv("cleaned_data\\movie_actor_2.csv", index=False)
df_rating.to_csv("cleaned_data\\movie_rating_2.csv", index=False)
df_genre.to_csv("cleaned_data\\movie_genre_2.csv", index=False)
All dataframes have imdbID. So it can be used as primary key
The main table will be df_movie. In order for imdbID to be Primary Key, os there shouldn't be any duplicates in rows and NaN or NA in imdbID column.¶
Does imdbID have duplicates or any Na/NaN rows?
In [30]:
df_movie[df_movie.duplicated()]
Out[30]:
No duplicates.
In [31]:
df_movie[df_movie['imdbID'].isnull()]
Out[31]:
One dummy row.
Remove the dummy row.¶
In [32]:
df_movie = df_movie[df_movie['imdbID'].notnull()]
Save the cleaned file as csv file again.¶
In [33]:
df_movie.to_csv("cleaned_data\\movie_movies_2.csv", index=False)
There are five data frames in total. The column every data frame has is imdbID. The main table for this data should be Movie_Movies.csv because it has one-to-one match between Title and imdbID. I am going to put the data into SQL now.
In [34]:
Image(filename='movie_record.png')
Out[34]:
Import all the data into MS-SQL Server¶
Diagram of the database¶
In [35]:
Image(filename='tables.png')
Out[35]:
In [36]:
Image(filename='diagram.png')
Out[36]:
Exploratory Data Analysis (EDA)¶
Quation1: How many movies the Top10 directors in the data set produced?¶
Load data from SQL database¶
In [37]:
conn = pyodbc.connect('Driver={SQL Server};'
'Server=DESKTOP-MB0G47L;'
'Database=test;'
'Trusted_Connection=yes;')
In [38]:
query_first = "SELECT Director, Title, imdbID FROM dbo.movie_movie_2 WHERE Type = 'Movie'" # only moive not series
In [39]:
df_first = pd.read_sql(query_first, conn)
Is there any None value in Director and Title columns?¶
In [40]:
df_first[df_first['Title'].isnull()]
Out[40]:
In [41]:
df_first[df_first['Director'].isnull()]
Out[41]:
Remove the None rows¶
In [42]:
df_first = df_first[df_first['Director'].notnull()]
Visual check¶
In [43]:
Image(filename='SQL1.png')
Out[43]:
We can see some rows with more than one names.
Explode , separated directors¶
In [44]:
df_first['Director'] = df_first['Director'].str.split(",") # make it as a list - comma separated
In [45]:
df_first = df_first.explode('Director') # explode the lists
Have new rows created?¶
In [46]:
df_first[df_first['Title'].duplicated()]
Out[46]:
Remove (co-director) from elements of the Director column¶
In [47]:
df_first['Director'] = df_first['Director'].str.replace(r"\(.*\)","")
Does space and capital/lower affect analysis?¶
Current result¶
In [48]:
df_first['Director'].value_counts().head(10)
Out[48]:
Capital or lower case?¶
In [49]:
df_first['Director'].str.lower().value_counts().head(10)
Out[49]:
Any white space?¶
In [50]:
df_first['Director'].str.strip().value_counts().head(10)
Out[50]:
White space affect the reult. Remove whitespace¶
In [51]:
df_first['Director'] = df_first['Director'].str.strip()
Are there duplicates in Director and Title columns?¶
In [52]:
df_first[df_first.duplicated()]
Out[52]:
Drop duplicates¶
In [53]:
df_first.drop_duplicates(keep='first', inplace =True)
Doesn't affect the result.¶
Top 10 directors by movies produced¶
In [54]:
names = list(df_first.groupby('Director')['Title'].count().sort_values(ascending = False).head(10).index)
movies = list(df_first.groupby('Director')['Title'].count().sort_values(ascending = False).head(10).values)
plt.figure(figsize=(14,6));
df_first.groupby('Director')['Title'].count().sort_values(ascending = False).head(10)[::-1].plot.barh(color=(0.9, 0.9, 0.9, 0.9), edgecolor='blue');
plt.title("Top10 directors by moives produced", fontsize=20);
plt.xlabel('Number of Movies', fontsize=14);
plt.ylabel('Directors', fontsize=14);
for count, i in enumerate(movies[::-1]):
plt.text(i, count, str(i), ha='left', va='center');
plt.grid(True);
print(df_first.groupby('Director')['Title'].count().sort_values(ascending = False).head(10))
The director who produced the most movies is Jim Powers with 157 moives. He is incomparable to any one on the Top 10 list. The second and third ranked directors are Dave Fleischer and D.W. Griffth with 109 and 104, respectively. The numbers of the 2nd and 3rd show a huge difference to the Top 1 director Jim Powers. So when it comes to making movies, Jim Powers has an extraordinary achievement. In addition, the directors from Top4 to Top10 have produced quite a similar number of moives, ranging from 83 to 95, showing a meaning gap between the Top3 group and the rest of Top 10 directors.
Quastion2: What kind of genre the directors in Top 10 list from Q1 make?¶
In [55]:
query_second = "SELECT dbo.movie_movie_2.imdbID, Director, Genre \
FROM dbo.movie_movie_2 \
JOIN dbo.movie_genre_2 \
ON movie_movie_2.imdbID = dbo.movie_genre_2.imdbID \
WHERE Director IS NOT NULL"
In [56]:
df_second = pd.read_sql(query_second, conn)
In [57]:
df_second.info()
In [58]:
df_second.head()
Out[58]:
Use the codes above in Q1 for cleaning the Director column¶
In [59]:
def name_cleaner(df):
df['Director'] = df['Director'].str.split(",") # make it as a list - comma separated
df = df.explode('Director') # explode the lists
df['Director'] = df['Director'].str.replace(r"\(.*\)","")
df['Director'] = df['Director'].str.strip()
df.drop_duplicates(keep='first', inplace =True)
return df
In [60]:
df_second_new = name_cleaner(df_second)
Pivot table for counts movies genre by director¶
In [61]:
df_second_new = df_second_new.pivot_table(index='Director', columns='Genre', values='imdbID', aggfunc= lambda x: len(x))
In [62]:
df_second_new.reset_index(inplace=True)
In [63]:
df_second_new.head(3)
Out[63]:
Get the top 10 list from Q1¶
In [64]:
df_top_ten = df_first.groupby('Director', as_index=False)['Title'].count().sort_values(ascending=False, by='Title').head(10)
In [65]:
df_top_ten.reset_index(drop=True, inplace=True)
In [66]:
df_top_ten.columns = ['Director', 'Counts']
Merge two data frame df_top_ten and df_second_new - Inner Join¶
In [67]:
df_top_ten
Out[67]:
Merge two dataframe¶
In [68]:
df_merged = df_top_ten.merge(df_second_new, left_on='Director', right_on = 'Director')
In [69]:
df_merged.head()
Out[69]:
In [70]:
fig, ([ax1, ax2], [ax3, ax4]) = plt.subplots(2, 2, figsize=(16,16))
ax1.pie(df_merged.iloc[0][2:][df_merged.iloc[0][2:].notnull()], labels=df_merged.iloc[0][2:][df_merged.iloc[0][2:].notnull()].index)
ax1.set_title("Jim Powers Top1")
ax2.pie(df_merged.iloc[1][2:][df_merged.iloc[1][2:].notnull()], labels=df_merged.iloc[1][2:][df_merged.iloc[1][2:].notnull()].index)
ax2.set_title("Dave Fleischer Top2")
ax3.pie(df_merged.iloc[2][2:][df_merged.iloc[2][2:].notnull()], labels=df_merged.iloc[2][2:][df_merged.iloc[2][2:].notnull()].index)
ax3.set_title("D.W. Griffith Top3")
ax4.pie(df_merged.iloc[3][2:][df_merged.iloc[3][2:].notnull()], labels=df_merged.iloc[3][2:][df_merged.iloc[3][2:].notnull()].index)
ax4.set_title("Lewin Fitzhamon Top4")
plt.show()
I will take a look at only first four directos from the top. Interestingly, it seems that the Top1 director Jim Powers is an adult film director. Most of his films are in the adult gerne. In contrast, as for the Top 2 director, he's a film director for children as his films are mostly related to Animation and Comedy. For the rest of the directors (D.W. Griffith and Lewin Fitzhamon), it is not so obvious what their films are about. I assume that their work is relatively diverse than first two directos.
Quastion3: Can we see a hype of specific genre over the past years?¶
In [71]:
query_third = "SELECT DISTINCT movie_movie_2.imdbID, [YEAR], Genre \
FROM dbo.movie_movie_2 \
FULL JOIN dbo.movie_genre_2 \
ON dbo.movie_movie_2.imdbID = movie_genre_2.imdbID \
WHERE Genre IS NOT NULL"
In [72]:
df_third = pd.read_sql(query_third, conn)
In [73]:
df_third.info()
In [74]:
df_third.head(3)
Out[74]:
In [75]:
df_third['Genre'].value_counts()
Out[75]:
In [76]:
df_third['YEAR'].value_counts().tail(50)
Out[76]:
Remove unreasonable data 2021, 2021.0, 2022, 2010-2013, 2007-2011, 2003-2005, 2012-2016, 2003-2005¶
In [77]:
df_third = df_third[(df_third['YEAR'] != '2021') & (df_third['YEAR'] != '2021.0') & (df_third['YEAR'] != '2022') & (df_third['YEAR'] != '2010–2013') & (df_third['YEAR'] != '2007–2011') & (df_third['YEAR'] != '2003–2005') & (df_third['YEAR'] != '2012–2016') & (df_third['YEAR'] != '2023')]
Change data type of YEAR into int¶
In [78]:
df_third['YEAR'] = df_third['YEAR'].astype(float).astype(int)
Since the data is upto 2017, we will not consider the data after 2017¶
In [79]:
df_third = df_third[df_third['YEAR'] <= 2017]
Quick overview of the total number of moives by year¶
In [80]:
plt.figure(figsize=(17,8));
df_third.groupby(['YEAR'])['imdbID'].count().plot.bar();
plt.ylabel('Counts', fontsize=14);
plt.xlabel('Year', fontsize=14);
plt.title('Movie by year', fontsize=18);
Change the dataframe into plotable data using pivot table.¶
In [81]:
df_third_new = df_third.pivot_table(index='YEAR', columns='Genre', values='imdbID', aggfunc= lambda x: len(x))
Simple information for the created dataframe.¶
In [82]:
df_third_new.head(3)
Out[82]:
In [83]:
df_third_new.describe()
Out[83]:
This time I am going to use plotly for a interative diagram.¶
In [84]:
import dash
import plotly.graph_objects as go
import plotly.offline as py
Make a list for plot data¶
In [85]:
data_bar_list = list(df_third_new)
Make a list of plot data using list comprehension¶
In [86]:
data_bar = [go.Bar(
x=df_third_new.index,
y=df_third_new[genre].values,
name=genre,
) for genre in data_bar_list]
Layout for the plot¶
In [87]:
layout_bar = go.Layout(
title='Movie Genres by Year',
barmode='stack',
yaxis_title='Number of movies',
xaxis_title='Year',
legend=dict(
x=-0.1,
y=-0.1,
orientation="h")
)
fig_bar = go.Figure(data=data_bar, layout=layout_bar)
In [88]:
fig_bar.update_layout(
autosize=False,
width=1000,
height=700
)
In [90]:
fig_bar.write_html("file.html")
The number of films produced per year has been increasing since 1920. One interesting fact from the plot above is that, for some reason, there was a short film boom between 1905 and 1919. As can be seen, there was a peak around 1910, then it drastically decreased. It seems that there might be an negative event that affected the film industry. So the early development of the film industry was ended due to the event. Since then the movie industry had a long plateau until 1980s. However, all of a sudden in 1990s the industry started booming and the number of films has been exponentially increasing until 2015. The most noticable genres in trend are drama, comedy and documentary (except for short), while one of the decreasing gernes is adult.